﻿using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.ApplicationBlocks.Data;

namespace maskx.JqGridHelper
{
    public class JqGridSQLHelper
    {
        #region public method
        public static string[] GetColnames(string connStr, string viewName)
        {
            string hashKey = viewName + "：" + connStr;
            string[] cols = null;
            if (ColsCache.ContainsKey(hashKey))
            {
                cols = ColsCache[hashKey];
            }
            else
            {
                List<string> colsList = new List<string>();

                using (var reader = SqlHelper.ExecuteReader(connStr, CommandType.Text, selectcolcmd, new SqlParameter("t", viewName)))
                {
                    while (reader.Read())
                    {
                        colsList.Add(reader.GetString(0));
                    }
                }
                cols = colsList.ToArray();
                ColsCache.TryAdd(hashKey, cols);
            }
            return cols;
        }
        public static string BuildQueryOrder(string viewname,JqGridPars pars)
        {
            if (string.IsNullOrEmpty(pars.sidx))
                return string.Empty;

            string s = string.Empty;
            var sidx = SplitCSV(pars.sidx);
            var sord = SplitCSV(pars.sord);
            for (int i = 0; i < sidx.Length; i++)
            {
                s += viewname+"."+SafeObjectName(sidx[i]);
                if (i < sord.Length)
                {
                    s += " " + SafeOrder(sord[i]);
                }
                s += ",";
            }
            return s.TrimEnd(',');
        }
        public static string BuildQueryCondition(JqGridPars pars, List<SqlParameter> sqlpars)
        {
            StringBuilder sb = new StringBuilder();
            if (pars._search)
            {
                if (pars.filters == null)
                {
                    sb.Append(GetOp(pars, sqlpars));
                }
                else
                {
                    sb.Append(GetSearcheFilter(pars.filters, sqlpars));
                }
            }
            return sb.ToString();
        }
        public static string[] SplitCSV(string line)
        {
            System.Text.RegularExpressions.RegexOptions options = ((System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline)
                | System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            Regex reg = new Regex("(?:^|,)(\\\"(?:[^\\\"]+|\\\"\\\")*\\\"|[^,]*)", options);
            MatchCollection coll = reg.Matches(line);
            string[] items = new string[coll.Count];
            int i = 0;
            foreach (Match m in coll)
            {
                items[i++] = m.Groups[0].Value.Trim('"').Trim(',').Trim('"').Trim();
            }
            return items;
        }
        public static string SafeOrder(string sidx)
        {
            sidx = sidx.Trim().ToUpper();
            if (sidx == "ASC")
                return sidx;
            if (sidx == "DESC")
                return sidx;
            return string.Empty;
        }
        public static string SafeObjectName(string f)
        {
            string rtv = f;
            rtv = rtv.Replace("]", "]]");
            return "[" + rtv + "]";
        }
        #endregion

        #region privare method
        private static string SafeGroupOp(string groupOp)
        {
            if (string.Equals("or", groupOp, System.StringComparison.OrdinalIgnoreCase))
                return "or";
            else
                return "and";
        }
        private static string GetSearcheFilter(JqGridSearchFilter filter, List<SqlParameter> sqlpars)
        {
            StringBuilder sb = new StringBuilder();
            if (filter.rules != null)
            {
                for (int i = 0; i < filter.rules.Length; i++)
                {
                    sb.Append(" " + SafeGroupOp(filter.groupOp) + "  ");
                    sb.Append(GetRule(filter.rules[i], sqlpars));
                }
            }
            if (filter.groups != null)
            {
                string g = string.Empty;
                for (int i = 0; i < filter.groups.Length; i++)
                {
                    g = GetSearcheFilter(filter.groups[i], sqlpars);
                    if (!string.IsNullOrEmpty(g))
                    {
                        sb.Append(" " + SafeGroupOp(filter.groupOp) + "  (");
                        sb.Append(g);
                        sb.Append(") ");
                    }
                }
            }
            if (sb.Length < 4)
                return string.Empty;
            return sb.Remove(0, 4).ToString();

        }
        private static string GetRule(JqGridSearchRule rule, List<SqlParameter> sqlpars)
        {
            string f = rule.field;
            string v = rule.data;
            if (string.IsNullOrEmpty(f))
            {
                return string.Empty;
            }
            return BuildFilter(f, rule.op, v, sqlpars);
        }
        private static string GetOp(JqGridPars pars, List<SqlParameter> sqlpars)
        {
            string f = pars.searchField;
            string v = pars.searchString;
            if (string.IsNullOrEmpty(f))
            {
                return string.Empty;
            }
            return BuildFilter(f, pars.searchOper, v, sqlpars);
        }
        private static string BuildFilter(string f, string o, string v, List<SqlParameter> sqlpars)
        {
            f = SafeObjectName(f);
            string par = "v" + sqlpars.Count;
            switch (o)
            {
                case "nn":
                    return f + " is not null";
                case "nu":
                    return f + " is null";
                case "eq":
                    sqlpars.Add(new SqlParameter(par, v));
                    return f + " = @" + par;
                case "ne":
                    sqlpars.Add(new SqlParameter(par, v));
                    return f + " <> @" + par;
                case "lt":
                    sqlpars.Add(new SqlParameter(par, v));
                    return f + " < @" + par;
                case "le":
                    sqlpars.Add(new SqlParameter(par, v));
                    return f + " <= @" + par;
                case "gt":
                    sqlpars.Add(new SqlParameter(par, v));
                    return f + " > @" + par;
                case "ge":
                    sqlpars.Add(new SqlParameter(par, v));
                    return f + " >= @" + par;
                case "bw":
                    sqlpars.Add(new SqlParameter(par, v + "%"));
                    return f + " like @" + par;
                case "bn":
                    sqlpars.Add(new SqlParameter(par, v + "%"));
                    return f + " not like @" + par;
                case "ew":
                    sqlpars.Add(new SqlParameter(par, "%" + v));
                    return f + " like @" + par;
                case "en":
                    sqlpars.Add(new SqlParameter(par, "%" + v));
                    return f + " not like@" + par;
                case "cn":
                    sqlpars.Add(new SqlParameter(par, "%" + v + "%"));
                    return f + " like @" + par;
                case "nc":
                    sqlpars.Add(new SqlParameter(par, "%" + v + "%"));
                    return f + " not like  @" + par;
                case "in":
                    var pars1 = BuildInPar(v, sqlpars);
                    return f + " in (" + pars1 + ")";
                case "ni":
                    var pars2 = BuildInPar(v, sqlpars);
                    return f + " not in (" + pars2 + ")";
                default:
                    return string.Empty;
            }
        }
        private static string BuildInPar(string v, List<SqlParameter> sqlpars)
        {
            List<string> pars = new List<string>();
            var values = SplitCSV(v);
            foreach (var item in values)
            {
                string par = "v" + sqlpars.Count;
                pars.Add("@" + par);
                sqlpars.Add(new SqlParameter(par, item));
            }
            return string.Join(",", pars.ToArray());
        }
        #endregion

        #region member
        const string selectcolcmd = @"select b.name from sys.objects a left join  sys.columns b on a.object_id = b.object_id where a.name = @t order by column_id";
        static ConcurrentDictionary<string, string[]> ColsCache = new ConcurrentDictionary<string, string[]>();
        #endregion
    }
}
